﻿using CMS.Excep;
using CMS.Model;
using Microsoft.Data.Sqlite;
using System.Reflection;
using System.Text;
using SQLitePCL;
using System.Diagnostics.Metrics;
using System.Data;
using System.Data.Common;

namespace CMS.DataAccess
{
    public class DbInstance : IDisposable
    {
        static DbInstance()
        {        
            Batteries.Init();
        }

        const string _connectionString = "Data Source=data/cms.db";
        private SqliteConnection _connection;
        private SqliteTransaction _transaction;
        public DbInstance()
        {
            BeginTransaction();
        }
        public string ToInsertSql<T>(T data, out SqliteParameter[] parameters) where T : BaseModel, new()
        {
            var parameterList = new List<SqliteParameter>();
            StringBuilder stringBuilderIns = new StringBuilder();
            StringBuilder stringBuilderVal = new StringBuilder();
            var tableName = typeof(T).GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (String.IsNullOrWhiteSpace(tableName))
                throw new MySqlException($"生成SQL语句失败:{typeof(T)}");
            stringBuilderIns.Append($"INSERT INTO {tableName}(");
            stringBuilderVal.Append($" VALUES(");
            var props = typeof(T).GetProperties();
            for (int i = 0; i < props.Length; i++)
            {

                var val = props[i].GetValue(data);
                if (val == null) continue;
                if (i > 0)
                {
                    stringBuilderIns.Append(",");
                    stringBuilderVal.Append(",");
                }
                stringBuilderIns.Append(props[i].Name);
                stringBuilderVal.Append($"@{props[i].Name}");
                parameterList.Add(new SqliteParameter($"@{props[i].Name}", val));
                if (i == props.Length - 1)
                {
                    stringBuilderIns.Append(")");
                    stringBuilderVal.Append(")");
                }
            }
            parameters = parameterList.ToArray();
            return stringBuilderIns.Append(stringBuilderVal).ToString();
        }

        public string ToDeleteSql<T>(string pk,out SqliteParameter parameter) where T : BaseModel, new()
        {
            var tableName = typeof(T).GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (String.IsNullOrWhiteSpace(tableName))
                throw new MySqlException($"生成SQL语句失败:{typeof(T)}");
            parameter = new SqliteParameter($"@PK", pk);
            return $"UPDATE {tableName} SET DEL = 'Y' WHERE PK = @PK";
        }

        public string ToUpdateSql<T>(T data, out SqliteParameter[] parameters) where T : BaseModel, new()
        {
            StringBuilder stringBuilderUpd = new StringBuilder();
            var parameterList = new List<SqliteParameter>();
            var tableName = typeof(T).GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (String.IsNullOrWhiteSpace(tableName))
                throw new MySqlException($"生成SQL语句失败:{typeof(T)}");
            stringBuilderUpd.Append($"UPDATE {tableName} SET ");
            var props = typeof(T).GetProperties();
            for (int i = 0; i < props.Length; i++)
            {
                if (props[i].Name == nameof(BaseModel.Pk)
                    || props[i].Name == nameof(BaseModel.Flag)
                    || props[i].Name == nameof(BaseModel.Del)
                    || props[i].Name == nameof(BaseModel.CreateTime)
                    || props[i].Name == nameof(BaseModel.Creator))
                    continue;
                stringBuilderUpd.Append($"{props[i].Name}=@{props[i].Name},");
                var pValue = props[i].GetValue(data);
                if (pValue == null)
                    pValue = DBNull.Value;
                parameterList.Add(new SqliteParameter($"@{props[i].Name}", pValue));

            }
            stringBuilderUpd.AppendFormat("FLAG=FLAG+1 WHERE DEL = 'N' AND PK = @PK AND FLAG={0}", data.Flag);
            parameterList.Add(new SqliteParameter("@PK",data.Pk));
            parameters = parameterList.ToArray();
            return stringBuilderUpd.ToString();
        }

        public string ToCount<T>(CondCollection whereConds, out SqliteParameter[] parameters)
        {
            var parameterList = new List<SqliteParameter>();
            StringBuilder stringBuilderSelect = new StringBuilder();
            var tableName = typeof(T).GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (String.IsNullOrWhiteSpace(tableName))
                throw new MySqlException($"生成SQL语句失败:{typeof(T)}");
            stringBuilderSelect.Append($"SELECT COUNT(*) FROM {tableName} WHERE DEL = 'N'  ");
            int counter = 0;
            foreach (var w in whereConds)
            {
                stringBuilderSelect.Append($"AND {w.ToSql(counter)}");
                parameterList.Add(new SqliteParameter(w.SqlParamName+counter, w.Value));
                counter++;
            }
            parameters = parameterList.ToArray();
            return stringBuilderSelect.ToString();
        }
        public string ToSelectWithPage<T>(CondCollection whereConds,int pageSize,int pageIndex, out SqliteParameter[] parameters) where T : BaseModel, new()
        {
            var sql = ToSelect<T>(whereConds, out parameters);
            return $"{sql} LIMIT {pageSize} OFFSET {pageIndex * pageSize} ";
        }

        public string ToSelect<T>(CondCollection whereConds, out SqliteParameter[] parameters) where T : BaseModel, new()
        {
            var parameterList = new List<SqliteParameter>();
            StringBuilder stringBuilderSelect = new StringBuilder();
            var tableName = typeof(T).GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (String.IsNullOrWhiteSpace(tableName))
                throw new MySqlException($"生成SQL语句失败:{typeof(T)}");
            stringBuilderSelect.Append($"SELECT * FROM {tableName} WHERE DEL = 'N' ");
            int counter = 0;
            foreach (var w in whereConds)
            {
                stringBuilderSelect.Append($"AND {w.ToSql(counter)}");
                parameterList.Add(new SqliteParameter(w.SqlParamName+counter, w.Value));
                counter++;
            }
            stringBuilderSelect.Append($" ORDER BY ");
            if (whereConds.OrderBy.Count() > 0)
            {
                foreach (var orderby in whereConds.OrderBy)
                {
                    stringBuilderSelect.Append($" {orderby.Field} ");
                    if (orderby.Desc) stringBuilderSelect.Append(" DESC ");
                }
            }
            else
            {
                stringBuilderSelect.Append($" {nameof(BaseModel.CreateTime)} DESC ");
            }
            parameters = parameterList.ToArray();
            return stringBuilderSelect.ToString();
        }
        public SqliteCommand MakeCommand(string query, params SqliteParameter[] parameters)
        {
            try
            {
                SqliteCommand command = new SqliteCommand(query, _connection);
                command.Transaction = _transaction;
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command;
            }
            catch (Exception ex)
            {
                throw new MySqlException("执行SQL操作失败", ex);
            }
        }
        public int ExeSql(string query, params SqliteParameter[] parameters)
        {
            try
            {
                using (SqliteCommand command = new SqliteCommand(query, _connection))
                {
                    command.Transaction = _transaction;
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    return command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new MySqlException("执行SQL操作失败", ex);
            }
        }

        private void BeginTransaction()
        {
            _connection = new SqliteConnection(_connectionString);
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }
        public void Commit()
        {
            if(_transaction==null) throw new MySqlException("事务尚未开启无法提交");
            _transaction.Commit();
        }
        public void Rollback()
        {
            if (_transaction == null) throw new MySqlException("事务尚未开启无法提交");
            try
            {
                _transaction.Rollback();
            }
            catch { }
        }

        public void Dispose()
        {
            if (_connection != null)
            {
                _transaction.Dispose();
                _connection.Dispose();
            }
        }
    }
}